package cn.com.generaldata.os.dao.impl;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.impl.sql.SqlTemplate;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;

import cn.com.generaldata.jsme_indicator_system.entity.UnitNz;
import cn.com.generaldata.jsme_indicator_system.entity.VerifyReportDetail;
import cn.com.generaldata.os.constant.FilterConstant;
import cn.com.generaldata.os.constant.ReportConstant;
import cn.com.generaldata.os.dao.intf.BaseDAOInterface;
import cn.com.generaldata.os.newpojo.ReportType;
import cn.com.generaldata.os.newpojo.SystemCodedic;
import cn.com.generaldata.os.paging.Event;
import cn.com.generaldata.os.paging.Filter;
import cn.com.generaldata.os.paging.FilterRule;
import cn.com.generaldata.os.paging.Page;
import cn.com.generaldata.os.paging.Result;
import cn.com.generaldata.os.pojo.Area;
import cn.com.generaldata.os.pojo.Report;
import cn.com.generaldata.os.pojo.ReportUnit;
import cn.com.generaldata.os.pojo.User;

import com.google.common.collect.Maps;

public class ReportDAO extends BaseDAO<Report> {
	private static Logger log = LoggerFactory.getLogger(ReportDAO.class);
	private BaseDAOInterface<Area> areaDAO;
	private BaseDAOInterface<User> userDAO;
	private BaseDAOInterface<ReportUnit> reportUnitDAO;
	private BaseDAOInterface<ReportType> reportTypeDAO;
	private BaseDAOInterface<SystemCodedic> sysCodedicDAO;
	@Autowired
	private Dao dao;
	@Autowired
	private SqlTemplate sqlTemplate;

	public void setSysCodedicDAO(BaseDAOInterface<SystemCodedic> sysCodedicDAO) {
		this.sysCodedicDAO = sysCodedicDAO;
	}

	public void setReportTypeDAO(BaseDAOInterface<ReportType> reportTypeDAO) {
		this.reportTypeDAO = reportTypeDAO;
	}

	public void setReportUnitDAO(BaseDAOInterface<ReportUnit> reportUnitDAO) {
		this.reportUnitDAO = reportUnitDAO;
	}

	public void setUserDAO(BaseDAOInterface<User> userDAO) {
		this.userDAO = userDAO;
	}

	public void setAreaDAO(BaseDAOInterface<Area> areaDAO) {
		this.areaDAO = areaDAO;
	}

	public String getShengId(String areaId) {
		String id = "";
		Area areaById = areaDAO.get(Area.class, areaId);
		if (!"0".equals(areaById.getParentId())) {
			id = getShengId(areaById.getParentId());
		} else {
			id = areaById.getId();
		}
		return id;
	}

	public List<VerifyReportDetail> getVerifyReortDetail(String checkStatus, String reporTypeId,
			Date startingReportDate, Date endingReportDate, List<String> areaIds, String flag, String unitId,
			String year) {
		String hql = "SELECT * from v_report_detail vrd inner join tbl_report r on vrd.report_id=r.id inner join tbl_area_perform ap on vrd.AERAID=ap.id where vrd.REPORT_TYPE_ID=@reporTypeId and exists(select id from tbl_unit_relation tur where tur.pid=@unitId and r.unit_id=tur.cid)";

		//		String hql = "select rd.*,r.reportAreaId aeraId,u.userUnit unitId from ReportDetailNz as rd,ReportNz r,tbl_user u where exists(r.id=rd.reportId and r.reportUserId=u.id and r.reportTypeId=:reporTypeId and r.reportAreaId in :areaIds";
		HashMap map = new HashMap();
		map.put("reporTypeId", reporTypeId);
		map.put("unitId", unitId);
		//需要审核
		if ("0".equals(flag) && !StringUtils.isBlank(checkStatus)) {
			//上级海洋局
			HashMap param = new HashMap();
			String sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select pid from TBL_UNIT_RELATION where cid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			param.put("unitId", unitId);
			List<UnitNz> listP = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int pusers = 0;
			if (listP != null)
				pusers = listP.size();
			// 下级海洋局
			sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select cid from TBL_UNIT_RELATION where pid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			List<UnitNz> listC = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int cids = 0;
			if (listC != null)
				cids = listC.size();
			//待审批
			if (ReportConstant.FLAG_COMMITTED == Integer.valueOf(checkStatus)) {
				//深度0层提交的默认为终审通过，所以不用查询
				//查询深度1层提交的和终审通过,自己不是顶级报表不能是直报
				//（如果有上级，不能审批直报））
				if (pusers > 0)
					hql += " and (((exists(select id from tbl_unit_relation tur where tur.pid=@unitId"
							+ " and (exists (select tu.id from tbl_user tu where tur.cid=tu.USER_UNIT and tu.id=r.REPORT_OPERATOR_ID)) and tur.depth="
							+ ReportConstant.INTEGER_TYPE + "))"
							+ " and not exists(select rt.id from tbl_report_type rt where rt.DIRECT_SUMBIT_PROVINCE="
							+ ReportConstant.INTEGER_TYPE + " and rt.id=r.REPORT_TYPE_ID))";
				else
					hql += " and ((((exists(select id from tbl_unit_relation tur where tur.pid=@unitId"
							+ " and (exists (select tu.id from tbl_user tu where tur.cid=tu.USER_UNIT and tu.id=r.REPORT_OPERATOR_ID)) and tur.depth="
							+ ReportConstant.INTEGER_TYPE
							+ "))"
							+ " and not exists(select rt.id from tbl_report_type rt where rt.DIRECT_SUMBIT_PROVINCE="
							+ ReportConstant.INTEGER_TYPE
							+ " and rt.id=r.REPORT_TYPE_ID)) or ((exists(select id from tbl_unit_relation tur where tur.pid=@unitId"
							+ " and (exists (select tu.id from tbl_user tu where tur.cid=tu.USER_UNIT and tu.id=r.REPORT_OPERATOR_ID)) and tur.depth<>"
							+ ReportConstant.BYTE_TYPE + "))"
							+ " and exists(select rt.id from tbl_report_type rt where rt.DIRECT_SUMBIT_PROVINCE="
							+ ReportConstant.INTEGER_TYPE + " and rt.id=r.REPORT_TYPE_ID)))";
				hql += " and (vrd.report_check_status=@reportCheckStatus or vrd.report_check_status="
						+ ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED + "))";
				map.put("reportCheckStatus", checkStatus);
				map.put("unitId", unitId);
			}
		} else if (!StringUtils.isBlank(checkStatus)) {

			hql += " and vrd.REPORT_CHECK_STATUS=@checkStatus";
			map.put("checkStatus", checkStatus);

		}
		if (StringUtils.isNotBlank(year)) {
			hql += " and exists(select r.id from tbl_report r where r.report_year=@year and r.id=vrd.report_id)";
			map.put("year", year);
		}
		if (startingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and vrd.REPORT_MONTH >=@startDate";
			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}
		if (endingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and vrd.REPORT_MONTH <=@endDate";
			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);

		}
		//		hql += ")";
		hql += " order by ap.area_zimu_code,vrd.report_id,vrd.row_index asc";
		List<VerifyReportDetail> list = sqlTemplate.query(hql, map, dao.getEntity(VerifyReportDetail.class));

		return list;
	}

	@SuppressWarnings("unchecked")
	public Result queryAllReport(String unitId, String unitName, String reportSystem, String reportTypeId,
			String industryId, String reportVersion, Date startingReportDate, Date endingReportDate, Integer pageNum,
			Page page, String status, String year) {
		String hql = "from Report as r where 1=1";
		HashMap map = new HashMap();
		//不是省级就看自己和下级，否则都看
		//		if (!"320000000000000".equals(areaId)) {
		hql += " and exists(select id from UnitRelation ur where ur.pid=:unitId and ur.cid=r.unitId)";
		map.put("unitId", unitId);
		//		}
		User user = new User();
		if (StringUtils.isNotBlank(unitName)) {
			hql += " and exists (select id from User us where r.user.id=us.id and exists(select ut.id from Unit ut where ut.unitName like :unitName and us.userUnit.id=ut.id))";
			map.put("unitName", "%" + unitName + "%");
		}
		if (StringUtils.isNotBlank(reportTypeId)) {
			hql += " and reportTypeId=:reportTypeCode";
			map.put("reportTypeCode", reportTypeId);
		} else if (StringUtils.isNotBlank(reportSystem)) {

			if (reportVersion != null && !"".equals(reportVersion)) {
				if (StringUtils.isNotBlank(industryId)) {

					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId and exists(select id from DustryReport dr where dr.industry.id=:industryId and dr.reportTypeId=rt.id))";
					map.put("industryId", industryId);
				} else {

					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId)";

				}
				map.put("versionId", Integer.valueOf(reportVersion));
			} else {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.id=r.reportTypeId)";
			}
			map.put("statSystem", Integer.valueOf(reportSystem));
		}
		if (StringUtils.isNotBlank(status)) {
			hql += " and reportCheckStatus=:status";
			map.put("status", Short.valueOf(status));
		}
		if (StringUtils.isNotBlank(year)) {
			hql += " and r.reportYear=:year";
			map.put("year", year);
		}
		if (startingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth >=:startDate";
			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}

		if (endingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth <=:endDate";
			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);

		}
		hql += " order by reportLastEditTime desc";
		Query query = getSession().createQuery(hql);
		if (map != null) {
			query.setProperties(map);
		}
		//		String sum = "select count(*) as count " + hql;
		String sum = "";
		int fIndex = hql.indexOf("from");
		if (fIndex != -1) {
			sum = "select count(*) as count " + hql.substring(fIndex);
		}
		int oIndex = sum.indexOf("order by");
		if (oIndex != -1) {
			sum = sum.substring(0, oIndex);
		}
		System.out.println("原sql：" + hql);
		System.out.println("新sql：" + sum);
		Query querySum = getSession().createQuery(sum);
		if (map != null) {
			querySum.setProperties(map);
		}
		int count = ((Number) querySum.iterate().next()).intValue();
		if (page == null) {
			//			query.setFirstResult(0);
			//			query.setMaxResults(20);
		} else {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());

		}
		List<Report> findByHql = query.list();

		if (page == null) {
			return new Result(1, 1, count, findByHql, new ArrayList<Event>());
		} else {
			return new Result(page.getPageNumber(), page.getPageSize(), count, findByHql, new ArrayList<Event>());
		}
	}

	@SuppressWarnings("unchecked")
	public Result queryReportsByParent(String reportProxyId, String userId, Short checkStatus, String reportSystem,
			String reportTypeCode, String reportVersion, Date startingReportDate, Date endingReportDate, String areaId,
			Integer pageNum, Page page, String year) {
		String hql = "from Report as r where 1=1";
		HashMap map = new HashMap();
		// 代填用户
		User user = new User();
		if (reportProxyId != null) {
			user = userDAO.get(User.class, reportProxyId);
		}

		if (checkStatus != null) {
			// 省级海洋局的用户
			UnitNz unitP = dao.fetch(
					UnitNz.class,
					Cnd.where("unitRoleId", "=", ReportConstant.OCEAN).and("unitAreaId", "=",
							ReportConstant.PROVINCE_AREA_ID));

			// 上级海洋局的用户
			Area areaById = areaDAO.get(Area.class, areaId);
			//上级海洋局
			HashMap param = new HashMap();
			String sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select pid from TBL_UNIT_RELATION where cid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			param.put("unitId", user.getUserUnit().getId());
			List<UnitNz> listP = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int pusers = 0;
			if (listP != null)
				pusers = listP.size();
			// 下级海洋局
			sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select cid from TBL_UNIT_RELATION where pid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			List<UnitNz> listC = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int cids = 0;
			if (listC != null)
				cids = listC.size();

			// 驳回列表(查询上级终审驳回且是驳回给自己或自己下级的)
			if (ReportConstant.FLAG_PROVINCE_REJECTED == checkStatus) {

				//自己上级终审驳回且是填报的,不能审批非自己提交的直驳
				hql += " and (reportCheckStatus="
						+ ReportConstant.FLAG_PROVINCE_REJECTED
						+ " and exists (select us.id from User as us where us.id=r.user.id and us.userUnit.id=:unitId or us.id=r.reportProxyId)";
				//						+ " and exists(select id from UnitRelation tur where tur.cid=:unitId and (exists (select tu.id from User tu where tur.pid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth="
				//						+ ReportConstant.INTEGER_TYPE
				//						+ ") and exists(select id from UnitRelation ur where ur.pid=:unitId and ur.cid=r.unitId)";
				//（如果有下级，不能审批直驳））
				if (cids > 0) {
					hql += " and not exists(select rt.id from ReportType rt where rt.directProvince="
							+ ReportConstant.INTEGER_TYPE + " and rt.id=r.reportTypeId and r.unitId<>:unitId)";
				}
				hql += ")";

				map.put("unitId", user.getUserUnit().getId());
			}
			// 通过列表（省级终审通过且是给自己或自己下级的）
			else if (ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED == checkStatus) {
				hql += " and (reportCheckStatus=" + ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED;
				if (unitP != null) {
					hql += " and exists (select us.id from User as us where us.id=r.operator.id and us.role.id='"
							+ ReportConstant.OCEAN
							+ "' and us.userUnit.id=:sid) and exists(select id from UnitRelation ur where ur.pid=:unitId and ur.cid=r.unitId)";
					map.put("sid", unitP.getId());
					map.put("unitId", user.getUserUnit().getId());
				}
				hql += ")";
			}
			// 已提交（本身终审通过且没有省终审和上级驳回的）
			else if (ReportConstant.FLAG_COMMITTED == checkStatus) {
				Filter ruFilter = new Filter();
				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "checkStatus", Short
						.valueOf(ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED)));
				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "unitId", user.getUserUnit().getId()));
				int ruids = reportUnitDAO.count(ReportUnit.class, ruFilter);
				if (ruids > 0) {
					hql += " and  exists (select ru.id from ReportUnit as ru where ru.reportId=r.id and ru.checkStatus="
							+ ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED + " and ru.unitId=:unitId)";
					map.put("unitId", user.getUserUnit().getId());
					if (unitP != null) {
						hql += " and (not exists (select uss.id from User as uss where uss.id=r.operator.id and uss.role.id='"
								+ ReportConstant.OCEAN
								+ "' and uss.userUnit.id=:sid) or reportCheckStatus<>"
								+ ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED + ")";
						map.put("sid", unitP.getId());
					}
					// 有上级海洋局
					if (pusers > 0) {
						hql += " and (not exists(select id from UnitRelation tur where tur.cid=:unitId and (exists (select tu.id from User tu where tur.pid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth="
								+ ReportConstant.INTEGER_TYPE
								+ ") or reportCheckStatus<>"
								+ ReportConstant.FLAG_PROVINCE_REJECTED + ")";
						map.put("unitId", user.getUserUnit().getId());
					}
				} else {
					return new Result(1, 1, 0, new ArrayList<Report>(), new ArrayList<Event>());
				}
			}
		} else {
			return new Result(1, 1, 0, new ArrayList<Report>(), new ArrayList<Event>());
		}

		if (StringUtils.isNotBlank(reportTypeCode)) {
			hql += " and reportTypeId=:reportTypeCode";
			map.put("reportTypeCode", reportTypeCode);
		} else if (!StringUtils.isNotBlank(reportTypeCode) && StringUtils.isNotBlank(reportSystem)) {

			if (reportVersion != null && !"".equals(reportVersion)) {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId)";

				map.put("versionId", Integer.valueOf(reportVersion));
			} else {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.id=r.reportTypeId)";

			}
			map.put("statSystem", Integer.valueOf(reportSystem));
		}
		if (StringUtils.isNotBlank(year)) {
			hql += " and r.reportYear=:year";
			map.put("year", year);
		}
		if (startingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth >=:startDate";
			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}

		if (endingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth <=:endDate";
			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);

		}

		hql += " order by reportLastEditTime desc";

		Query query = getSession().createQuery(hql);
		if (map != null) {
			query.setProperties(map);
		}
		String sum = "";
		int fIndex = hql.indexOf("from");
		if (fIndex != -1) {
			sum = "select count(*) as count " + hql.substring(fIndex);
		}
		int oIndex = sum.indexOf("order by");
		if (oIndex != -1) {
			sum = sum.substring(0, oIndex);
		}
		Query querySum = getSession().createQuery(sum);
		if (map != null) {
			querySum.setProperties(map);
		}
		int count = ((Number) querySum.iterate().next()).intValue();
		if (page == null) {
			query.setFirstResult(0);
			query.setMaxResults(20);
		} else {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());

		}
		List<Report> findByHql = query.list();

		if (page == null) {
			return new Result(1, 1, count, findByHql, new ArrayList<Event>());
		} else {
			return new Result(page.getPageNumber(), page.getPageSize(), count, findByHql, new ArrayList<Event>());
		}
	}

	public Result queryReportsByParentForWebUnit(String reportProxyId, String userId, Short checkStatus,
			String reportSystem, String reportTypeCode, String reportVersion, Date startingReportDate,
			Date endingReportDate, String areaId, Integer pageNum, Page page, String year) {

		String hql = "from Report as r where 1=1";
		HashMap map = new HashMap();
		// 代填用户
		User user = new User();
		if (reportProxyId != null) {
			user = userDAO.get(User.class, reportProxyId);
		}
		if (checkStatus != null) {
			// 省级海洋局的用户
			UnitNz unitP = dao.fetch(
					UnitNz.class,
					Cnd.where("unitRoleId", "=", ReportConstant.OCEAN).and("unitAreaId", "=",
							ReportConstant.PROVINCE_AREA_ID));

			// 上级海洋局的用户
			Area areaById = areaDAO.get(Area.class, areaId);
			//上级海洋局
			HashMap param = new HashMap();
			String sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select pid from TBL_UNIT_RELATION where cid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			param.put("unitId", user.getUserUnit().getId());
			List<UnitNz> listP = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int pusers = 0;
			if (listP != null)
				pusers = listP.size();
			// 下级海洋局
			sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select cid from TBL_UNIT_RELATION where pid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			List<UnitNz> listC = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int cids = 0;
			if (listC != null)
				cids = listC.size();

			// 驳回列表(查询上级终审驳回且是驳回给自己或自己下级的)
			if (ReportConstant.FLAG_PROVINCE_REJECTED == checkStatus) {

				//自己上级终审驳回且是填报或代填的,不能审批非自己提交的直驳
				hql += " and (reportCheckStatus=" + ReportConstant.FLAG_PROVINCE_REJECTED
						+ " and exists (select us.id from User as us where us.id=r.user.id and us.userUnit.id=:unitId)";
				// or us.id=r.reportProxyId)
				//						+ " and exists(select id from UnitRelation tur where tur.cid=:unitId and (exists (select tu.id from User tu where tur.pid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth="
				//						+ ReportConstant.INTEGER_TYPE
				//						+ ") and exists(select id from UnitRelation ur where ur.pid=:unitId and ur.cid=r.unitId)";
				//（如果有下级，不能审批直驳））
				if (cids > 0) {
					hql += " and not exists(select rt.id from ReportType rt where rt.directProvince="
							+ ReportConstant.INTEGER_TYPE + " and rt.id=r.reportTypeId and r.unitId<>:unitId)";
				}
				hql += ")";

				map.put("unitId", user.getUserUnit().getId());
			}
			// 通过列表（省级终审通过且是给自己或自己下级的）
			else if (ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED == checkStatus) {
				hql += " and (reportCheckStatus=" + ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED;
				if (unitP != null) {
					hql += " and exists (select us.id from User as us where us.id=r.operator.id and us.role.id='"
							+ ReportConstant.OCEAN
							+ "' and us.userUnit.id=:sid) and exists(select id from UnitRelation ur where ur.pid=:unitId and ur.cid=r.unitId)";
					map.put("sid", unitP.getId());
					map.put("unitId", user.getUserUnit().getId());
				}
				hql += ")";
			}
			// 已提交（本身终审通过且没有省终审和上级驳回的）
			else if (ReportConstant.FLAG_COMMITTED == checkStatus) {
				Filter ruFilter = new Filter();
				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "checkStatus", Short
						.valueOf(ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED)));
				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "unitId", user.getUserUnit().getId()));
				int ruids = reportUnitDAO.count(ReportUnit.class, ruFilter);
				if (ruids > 0) {
					hql += " and  exists (select ru.id from ReportUnit as ru where ru.reportId=r.id and ru.checkStatus="
							+ ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED + " and ru.unitId=:unitId)";
					map.put("unitId", user.getUserUnit().getId());
					if (unitP != null) {
						hql += " and (not exists (select uss.id from User as uss where uss.id=r.operator.id and uss.role.id='"
								+ ReportConstant.OCEAN
								+ "' and uss.userUnit.id=:sid) or reportCheckStatus<>"
								+ ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED + ")";
						map.put("sid", unitP.getId());
					}
					// 有上级海洋局
					if (pusers > 0) {
						hql += " and (not exists(select id from UnitRelation tur where tur.cid=:unitId and (exists (select tu.id from User tu where tur.pid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth="
								+ ReportConstant.INTEGER_TYPE
								+ ") or reportCheckStatus<>"
								+ ReportConstant.FLAG_PROVINCE_REJECTED + ")";
						map.put("unitId", user.getUserUnit().getId());
					}
				} else {
					return new Result(1, 1, 0, new ArrayList<Report>(), new ArrayList<Event>());
				}
			}
		} else {
			return new Result(1, 1, 0, new ArrayList<Report>(), new ArrayList<Event>());
		}

		if (StringUtils.isNotBlank(reportTypeCode)) {
			hql += " and reportTypeId=:reportTypeCode";
			map.put("reportTypeCode", reportTypeCode);
		} else if (!StringUtils.isNotBlank(reportTypeCode) && StringUtils.isNotBlank(reportSystem)) {

			if (reportVersion != null && !"".equals(reportVersion)) {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId)";

				map.put("versionId", Integer.valueOf(reportVersion));
			} else {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.id=r.reportTypeId)";

			}
			map.put("statSystem", Integer.valueOf(reportSystem));
		}
		if (StringUtils.isNotBlank(year)) {
			hql += " and r.reportYear=:year";
			map.put("year", year);
		}
		if (startingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth >=:startDate";
			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}

		if (endingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth <=:endDate";
			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);

		}

		hql += " order by reportLastEditTime desc";

		Query query = getSession().createQuery(hql);
		if (map != null) {
			query.setProperties(map);
		}
		String sum = "";
		int fIndex = hql.indexOf("from");
		if (fIndex != -1) {
			sum = "select count(*) as count " + hql.substring(fIndex);
		}
		int oIndex = sum.indexOf("order by");
		if (oIndex != -1) {
			sum = sum.substring(0, oIndex);
		}
		Query querySum = getSession().createQuery(sum);
		if (map != null) {
			querySum.setProperties(map);
		}
		int count = ((Number) querySum.iterate().next()).intValue();
		if (page == null) {
			query.setFirstResult(0);
			query.setMaxResults(20);
		} else {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());

		}
		List<Report> findByHql = query.list();

		if (page == null) {
			return new Result(1, 1, count, findByHql, new ArrayList<Event>());
		} else {
			return new Result(page.getPageNumber(), page.getPageSize(), count, findByHql, new ArrayList<Event>());
		}
	}

	public Result queryReportsDelete(String proId, String userAreaId, String userUnit, String reportSystem,
			String reportTypeId, String industryId, String reportVersion, Date startingReportDate,
			Date endingReportDate, Integer pageNum, Page page, String typeNo) {
		String hql = "from Report as r where r.reportCheckStatus=5 and r.reportFlag='1' and exists(select id from User us where r.operator.id=us.id and us.role.id='4' and exists(select ut.id from Unit ut where ut.id =:proId and us.userUnit.id=ut.id))";
		HashMap map = new HashMap();
		map.put("proId", proId);
		if (userAreaId != null) {
			hql += " and r.area.id=:areaId";
			map.put("areaId", userAreaId);
		}
		if (StringUtils.isNotBlank(userUnit)) {
			hql += " and exists (select id from User us where r.user.id=us.id and exists(select ut.id from Unit ut where ut.unitName like :unitName and us.userUnit.id=ut.id))";
			map.put("unitName", "%" + userUnit + "%");
		}
		if (StringUtils.isNotBlank(reportTypeId)) {
			hql += " and reportTypeId=:reportTypeCode";
			map.put("reportTypeCode", reportTypeId);
		} else if (StringUtils.isNotBlank(reportSystem)) {
			if (reportVersion != null && !"".equals(reportVersion)) {
				if (StringUtils.isNotBlank(industryId)) {
					if (StringUtils.isNotBlank(typeNo)) {
						hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.typeNo like:typeNo and rt.versionId=:versionId and rt.id=r.reportTypeId and exists(select id from DustryReport dr where dr.industry.id=:industryId and dr.reportTypeId=rt.id))";
						map.put("typeNo", "%" + typeNo + "%");
					} else
						hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId and exists(select id from DustryReport dr where dr.industry.id=:industryId and dr.reportTypeId=rt.id))";
					map.put("industryId", industryId);
				} else {
					if (StringUtils.isNotBlank(typeNo)) {
						hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.typeNo like:typeNo and rt.versionId=:versionId and rt.id=r.reportTypeId)";
						map.put("typeNo", "%" + typeNo + "%");
					} else
						hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId)";

				}
				map.put("versionId", Integer.valueOf(reportVersion));
			} else {
				if (StringUtils.isNotBlank(typeNo)) {
					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.typeNo like:typeNo and rt.id=r.reportTypeId)";
					map.put("typeNo", "%" + typeNo + "%");
				} else
					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.id=r.reportTypeId)";
			}
			map.put("statSystem", Integer.valueOf(reportSystem));

		}
		if (startingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth >=:startDate";
			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}

		if (endingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth <=:endDate";
			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}
		hql += " order by reportLastEditTime desc";
		Query query = getSession().createQuery(hql);
		if (map != null) {
			query.setProperties(map);
		}
		String sum = "";
		int fIndex = hql.indexOf("from");
		if (fIndex != -1) {
			sum = "select count(*) as count " + hql.substring(fIndex);
		}
		int oIndex = sum.indexOf("order by");
		if (oIndex != -1) {
			sum = sum.substring(0, oIndex);
		}
		System.out.println("原sql：" + hql);
		System.out.println("新sql：" + sum);
		Query querySum = getSession().createQuery(sum);
		if (map != null) {
			querySum.setProperties(map);
		}
		int count = ((Number) querySum.iterate().next()).intValue();
		if (page == null) {
			query.setFirstResult(0);
			query.setMaxResults(20);
		} else {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());

		}
		List<Report> findByHql = query.list();

		if (page == null) {
			return new Result(1, 1, count, findByHql, new ArrayList<Event>());
		} else {
			return new Result(page.getPageNumber(), page.getPageSize(), count, findByHql, new ArrayList<Event>());
		}
	}

	@SuppressWarnings("unchecked")
	public Result queryReportsByProxy(String reportProxyId, String userId, Short checkStatus, String reportSystem,
			String reportTypeCode, String reportVersion, Date startingReportDate, Date endingReportDate, String areaId,
			Integer pageNum, Page page, String flag, String year, String unitName) {

		String hql = "from Report as r where 1=1";
		Map<String, Object> map = Maps.newHashMap();
		// 代填用户（当前登录用户）
		User user = new User();
		if (reportProxyId != null) {
			user = userDAO.get(User.class, reportProxyId);
		}
		if (flag != null) {
			if (userId != null) {
				// hql += " and user.id=:userId";
				// map.put("userId", userId);
				// 是数据填报
				if (userId.equals(reportProxyId)) {
					hql += " and unitId=:userUnit";
					map.put("userUnit", user.getUserUnit().getId());
				} else {
					hql += " and unitId<>:userUnit";
					map.put("userUnit", user.getUserUnit().getId());
				}
			} else {
				hql += " and unitId<>:userUnit";
				map.put("userUnit", user.getUserUnit().getId());
			}
		}
		if (StringUtils.isNotBlank(unitName)) {
			hql += " and exists (select id from User us where r.user.id=us.id and exists(select ut.id from Unit ut where ut.unitName like :unitName and us.userUnit.id=ut.id))";
			map.put("unitName", "%" + unitName + "%");
		}
		if (checkStatus != null) {
			//上级海洋局
			HashMap param = new HashMap();
			String sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select pid from TBL_UNIT_RELATION where cid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			param.put("unitId", user.getUserUnit().getId());
			List<UnitNz> listP = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int pusers = 0;
			if (listP != null)
				pusers = listP.size();
			// 下级海洋局
			sql = "select * from tbl_unit where UNIT_ROLE_ID='" + ReportConstant.OCEAN
					+ "' and id in(select cid from TBL_UNIT_RELATION where pid=@unitId and DEPTH<>"
					+ ReportConstant.BYTE_TYPE + ")";
			List<UnitNz> listC = sqlTemplate.query(sql, param, dao.getEntity(UnitNz.class));
			int cids = 0;
			if (listC != null)
				cids = listC.size();

			// 查询待审批(查询本级所有提交，上级终审驳回且是驳回给自己或自己下级的，下级终审通过)
			//(若是直报的只能省级查看)
			if (ReportConstant.FLAG_COMMITTED == checkStatus) {
				//深度0层提交的默认为终审通过，所以不用查询
				//查询深度1层提交的和终审通过,自己不是顶级报表不能是直报
				if (pusers > 0)
					hql += " and (((exists(select id from UnitRelation tur where tur.pid=:unitId"
							+ " and (exists (select tu.id from User tu where tur.cid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth="
							+ ReportConstant.INTEGER_TYPE
							+ ")) and not exists(select rt.id from ReportType rt where rt.directProvince="
							+ ReportConstant.INTEGER_TYPE + " and rt.id=r.reportTypeId))";
				else
					hql += " and ((((exists(select id from UnitRelation tur where tur.pid=:unitId"
							+ " and (exists (select tu.id from User tu where tur.cid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth="
							+ ReportConstant.INTEGER_TYPE
							+ ")) and not exists(select rt.id from ReportType rt where rt.directProvince="
							+ ReportConstant.INTEGER_TYPE
							+ " and rt.id=r.reportTypeId)) or ((exists(select id from UnitRelation tur where tur.pid=:unitId"
							+ " and (exists (select tu.id from User tu where tur.cid=tu.userUnit.id and tu.id=r.operator.id)) and tur.depth<>"
							+ ReportConstant.BYTE_TYPE
							+ ")) and exists(select rt.id from ReportType rt where rt.directProvince="
							+ ReportConstant.INTEGER_TYPE + " and rt.id=r.reportTypeId)))";
				hql += " and (reportCheckStatus=:reportCheckStatus or reportCheckStatus="
						+ ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED + "))";
				map.put("reportCheckStatus", checkStatus);
				map.put("unitId", user.getUserUnit().getId());
			}
			// 终审通过\待通过和待驳回\终审驳回
			else if (ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED == checkStatus
					|| ReportConstant.FLAG_PROVINCE_REJECTED == checkStatus
					|| ReportConstant.FLAG_PROVINCE_PRE_PASSED_COMMITTED == checkStatus
					|| ReportConstant.FLAG_PROVINCE_PRE_REJECTED == checkStatus) {
				Filter ruFilter = new Filter();
				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "checkStatus", checkStatus));
				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "unitId", user.getUserUnit().getId()));
				int ruids = reportUnitDAO.count(ReportUnit.class, ruFilter);
				if (ruids > 0) {
					hql += " and exists (select ru.reportId from ReportUnit as ru where ru.reportId=r.id and ru.checkStatus=:checkStatus and ru.unitId=:unitId)";
					map.put("checkStatus", checkStatus);
					map.put("unitId", user.getUserUnit().getId());
				} else {
					return new Result(1, 1, 0, new ArrayList<Report>(), new ArrayList<Event>());
				}
			}
			// 待上报（自己待上报的，终审驳回且是自己填的）
			else if (ReportConstant.FLAG_UNCOMMITTED == checkStatus) {
				hql += " and ((reportCheckStatus=:reportCheckStatus and (exists(select uc.id from User as uc where (uc.id=r.user.id or uc.id=r.reportProxyId) and uc.userUnit.id=:unitId)))";
				// 有上级海洋局
				// if (pusers.length > 0) {
				hql += " or (reportCheckStatus="
						+ ReportConstant.FLAG_PROVINCE_REJECTED
						+ " and (exists(select uc.id from User as uc where (uc.id=r.user.id or uc.id=r.reportProxyId) and uc.userUnit.id=:unitId))))";
				map.put("reportCheckStatus", checkStatus);
				map.put("unitId", user.getUserUnit().getId());

			}
			// 终审驳回
			else {
				hql += " and reportCheckStatus=:reportCheckStatus";
				map.put("reportCheckStatus", checkStatus);
			}
		} else {
			return new Result(1, 1, 0, new ArrayList<Report>(), new ArrayList<Event>());
		}

		if (StringUtils.isNotBlank(reportTypeCode)) {
			hql += " and reportTypeId=:reportTypeCode";
			map.put("reportTypeCode", reportTypeCode);
		} else if (!StringUtils.isNotBlank(reportTypeCode) && StringUtils.isNotBlank(reportSystem)) {

			if (reportVersion != null && !"".equals(reportVersion)) {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId)";

				map.put("versionId", Integer.valueOf(reportVersion));
			} else {

				hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.id=r.reportTypeId)";

			}
			map.put("statSystem", Integer.valueOf(reportSystem));

		}
		if (StringUtils.isNotBlank(year)) {
			hql += " and r.reportYear=:year";
			map.put("year", year);
		}
		//		hql += " and (reportReferenceId='' or reportReferenceId is null)";
		if (startingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(startingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth >=:startDate";
			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
		}

		if (endingReportDate != null) {
			Calendar calendar = Calendar.getInstance();
			calendar.setTime(endingReportDate);
			int month = calendar.get(Calendar.MONTH) + 1;
			String dateCurr = String.valueOf(month);
			if (month < 10)
				dateCurr = String.valueOf("0" + month);
			hql += " and reportMonth <=:endDate";
			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);

		}
		hql += " order by reportLastEditTime desc";

		Query query = getSession().createQuery(hql);
		if (map != null) {
			query.setProperties(map);
		}
		String sum = "";
		int fIndex = hql.indexOf("from");
		if (fIndex != -1) {
			sum = "select count(*) as count " + hql.substring(fIndex);
		}
		int oIndex = sum.indexOf("order by");
		if (oIndex != -1) {
			sum = sum.substring(0, oIndex);
		}
		Query querySum = getSession().createQuery(sum);
		if (map != null) {
			querySum.setProperties(map);
		}
		int count = ((Number) querySum.iterate().next()).intValue();
		if (page == null) {
			query.setFirstResult(0);
			query.setMaxResults(20);
		} else {
			query.setFirstResult(page.getFirstResult());
			query.setMaxResults(page.getPageSize());

		}
		List<Report> findByHql = query.list();

		if (page == null) {
			return new Result(1, 1, count, findByHql, new ArrayList<Event>());
		} else {
			return new Result(page.getPageNumber(), page.getPageSize(), count, findByHql, new ArrayList<Event>());
		}
	}

	//报表填报模块，查询使用这个。
	//	public Result queryReportsByProxy(String reportProxyId, String userId, Short checkStatus, String reportSystem,
	//			String reportTypeCode, String reportVersion, Date startingReportDate, Date endingReportDate, String areaId,
	//			Integer pageNum, Page page, String flag, String typeNo) {
	//
	//		String hql = "from Report as r where 1=1";
	//		HashMap map = new HashMap();
	//
	//		// 代填用户
	//		User user = new User();
	//		if (reportProxyId != null) {
	//			user = userDAO.get(User.class, reportProxyId);
	//		}
	//		if (flag != null) {
	//			if (userId != null) {
	//				// hql += " and user.id=:userId";
	//				// map.put("userId", userId);
	//				// 是数据填报
	//				if (userId.equals(reportProxyId)) {
	//					hql += " and reportUnit=:userUnit";
	//					map.put("userUnit", user.getUserUnit().getUnitName());
	//				} else {
	//					hql += " and reportUnit<>:userUnit";
	//					map.put("userUnit", user.getUserUnit().getUnitName());
	//				}
	//			} else {
	//				hql += " and reportUnit<>:userUnit";
	//				map.put("userUnit", user.getUserUnit().getUnitName());
	//			}
	//		}
	//		if (checkStatus != null) {
	//			// 上级海洋局的用户
	//			Area areaById = areaDAO.get(Area.class, areaId);
	//			Filter filter2 = new Filter();
	//			filter2.addRule(new FilterRule(FilterConstant.EQUAL, "area.id", areaById.getParentId()));
	//			filter2.addRule(new FilterRule(FilterConstant.EQUAL, "role.id", "4"));
	//			int pusers = userDAO.count(User.class, filter2);
	//			// String[] pusers = new String[result2.size()];
	//			// for (int i = 0; i < result2.size(); i++) {
	//			// pusers[i] = result2.get(i).getId();
	//			// }
	//			// 下级海洋局的区域
	//			Filter filter = new Filter();
	//			filter.setOrder(FilterConstant.ASC);
	//			filter.setSortBy("areaName");
	//			filter.addRule(new FilterRule(FilterConstant.NOT_NULL, "areaCode", null));
	//			filter.addRule(new FilterRule(FilterConstant.EQUAL, "parentId", areaId));
	//			List<Area> result = areaDAO.find(Area.class, filter, null);
	//			List<String> cusers = new ArrayList<String>();
	//			String[] careas = new String[result.size()];
	//			int cids = 0;
	//			for (int j = 0; j < result.size(); j++) {
	//				careas[j] = result.get(j).getId();
	//				Filter filter3 = new Filter();
	//				filter3.addRule(new FilterRule(FilterConstant.EQUAL, "area.id", result.get(j).getId()));
	//				filter3.addRule(new FilterRule(FilterConstant.EQUAL, "role.id", "4"));
	//				cids += userDAO.count(User.class, filter3);
	//				// for (int i = 0; i < result3.size(); i++) {
	//				// cusers.add(result3.get(i).getId());
	//				// }
	//			}
	//			// String[] cids = new String[cusers.size()];
	//			// for (int i = 0; i < cusers.size(); i++) {
	//			// cids[i] = cusers.get(i);
	//			// }
	//
	//			// 查询待审批(查询本级所有提交，上级终审驳回且是驳回给自己或自己下级的，下级终审通过)
	//			//(若是直报的只能省级查看)
	//			if (ReportConstant.FLAG_COMMITTED == checkStatus) {
	//				hql += " and ((reportCheckStatus=:reportCheckStatus and area.id=:areaId)";
	//				map.put("reportCheckStatus", checkStatus);
	//				map.put("areaId", areaId);
	//				// 有上级海洋局(不是省级)
	//				if (pusers > 0) {
	//					if (cids > 0) {
	//						//终审驳回且是驳回给自己或自己下级（市级）不能审批直驳
	//						hql += " or (reportCheckStatus=3 and exists (select up.id from User as up where up.id=r.operator.id and up.role.id='4' and up.area.id=:paid) and (area.id=:areaId1 or exists (select a.id from Area as a where r.area.id=a.id and a.areaCode is not null and a.parentId=:areaId1)) and not exists(select ui.id from User as ui where ui.id=r.operator.id and ui.role.id='4' and ui.area.id=:areaId1) and not exists(select rt.id from ReportType rt where rt.directProvince=1 and rt.id=r.reportTypeId))";
	//						map.put("paid", areaById.getParentId());
	//						map.put("areaId1", areaId);
	//					} else {
	//						//终审驳回且是驳回自己（县级）能看见直驳
	//						hql += " or (reportCheckStatus=3 and (exists (select up.id from User as up where up.id=r.operator.id and up.role.id='4' and up.area.id=:paid) or exists(select rt.id from ReportType rt where rt.directProvince=1 and rt.id=r.reportTypeId)) and area.id=:areaId1 and not exists(select ui.id from User as ui where ui.id=r.operator.id and ui.role.id='4' and ui.area.id=:areaId1))";
	//						map.put("paid", areaById.getParentId());
	//						map.put("areaId1", areaId);
	//					}
	//				}
	//				// 有下级海洋局
	//				if (cids > 0) {
	//					//（市级）不能审批直报
	//					if (pusers > 0) {
	//						hql += " or (reportCheckStatus=5 and exists(select u.id from User as u where r.operator.id=u.id and ((u.role.id=4 and exists(select au.id from Area as au where u.area.id=au.id and au.areaCode is not null and au.parentId=:areaId1)) or (u.role.id=5 and exists(select au.id from Area as au where u.area.id=au.id and au.areaCode is not null and au.id=:areaId1)))) and not exists(select rt.id from ReportType rt where rt.directProvince=1 and rt.id=r.reportTypeId))";
	//						map.put("areaId1", areaId);
	//					}
	//					//（省级）能审批直报
	//					else {
	//						hql += " or (reportCheckStatus=5 and exists(select u.id from User as u where r.operator.id=u.id and ((u.role.id=4 and exists(select au.id from Area as au where u.area.id=au.id and au.areaCode is not null and au.parentId=:areaId1)) or (u.role.id=5 and exists(select au.id from Area as au where u.area.id=au.id and au.areaCode is not null and au.id=:areaId1)))) or( not exists(select ui.id from User as ui where ui.id=r.operator.id and ui.role.id='4' and ui.area.id=:areaId1) and exists(select rt.id from ReportType rt where rt.directProvince=1 and rt.id=r.reportTypeId)))";
	//						map.put("areaId1", areaId);
	//					}
	//				}
	//				hql += ")";
	//			}
	//			// 终审通过\待通过和待驳回\终审驳回
	//			else if (ReportConstant.FLAG_PROVINCE_PASSED_COMMITTED == checkStatus
	//					|| ReportConstant.FLAG_PROVINCE_REJECTED == checkStatus
	//					|| ReportConstant.FLAG_PROVINCE_PRE_PASSED_COMMITTED == checkStatus
	//					|| ReportConstant.FLAG_PROVINCE_PRE_REJECTED == checkStatus) {
	//				Filter ruFilter = new Filter();
	//				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "checkStatus", checkStatus));
	//				ruFilter.addRule(new FilterRule(FilterConstant.EQUAL, "unitId", user.getUserUnit().getId()));
	//				int ruids = reportUnitDAO.count(ReportUnit.class, ruFilter);
	//				// String[] ruids = new String[queryReportUnit.size()];
	//				// for (int i = 0; i < queryReportUnit.size(); i++) {
	//				// ruids[i] = queryReportUnit.get(i).getReportId();
	//				// }
	//				if (ruids > 0) {
	//					hql += " and exists (select ru.reportId from ReportUnit as ru where ru.reportId=r.id and ru.checkStatus=:checkStatus and ru.unitId=:unitId)";
	//					map.put("checkStatus", checkStatus);
	//					map.put("unitId", user.getUserUnit().getId());
	//				} else {
	//					hql += " and 1=2";
	//				}
	//			}
	//			// 待上报（自己待上报的，终审驳回且是驳回给自己的）
	//			else if (ReportConstant.FLAG_UNCOMMITTED == checkStatus) {
	//				hql += " and (reportCheckStatus=:reportCheckStatus and (r.user.id=:userId or r.reportProxyId=:userId))";
	//				map.put("reportCheckStatus", checkStatus);
	//				map.put("userId", reportProxyId);
	//				// 填报
	//				if (userId.equals(reportProxyId))
	//					hql += " or (reportCheckStatus=3 and r.user.id=:userId1)";
	//				//代填
	//				else
	//					hql += " or (reportCheckStatus=3 and r.reportProxyId=:userId1)";
	//				map.put("userId1", reportProxyId);
	//				// map.put("iuser1", iuser);
	//
	//			}
	//			// 终审驳回
	//			else {
	//				hql += " and reportCheckStatus=:reportCheckStatus";
	//				map.put("reportCheckStatus", checkStatus);
	//			}
	//		} else {
	//			hql += " and 1=2";
	//		}
	//
	//		if (StringUtils.isNotBlank(reportTypeCode)) {
	//			hql += " and reportTypeId=:reportTypeCode";
	//			map.put("reportTypeCode", reportTypeCode);
	//		} else if (!StringUtils.isNotBlank(reportTypeCode) && StringUtils.isNotBlank(reportSystem)) {
	//
	//			if (reportVersion != null && !"".equals(reportVersion)) {
	//				if (StringUtils.isNotBlank(typeNo)) {
	//					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.typeNo like:typeNo and rt.versionId=:versionId and rt.id=r.reportTypeId)";
	//					map.put("typeNo", "%" + typeNo + "%");
	//				} else
	//					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.versionId=:versionId and rt.id=r.reportTypeId)";
	//
	//				map.put("versionId", Integer.valueOf(reportVersion));
	//			} else {
	//				if (StringUtils.isNotBlank(typeNo)) {
	//					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.typeNo like:typeNo and rt.id=r.reportTypeId)";
	//					map.put("typeNo", "%" + typeNo + "%");
	//				} else
	//					hql += " and exists (select id from ReportType rt where rt.statSystem=:statSystem and rt.id=r.reportTypeId)";
	//
	//			}
	//			map.put("statSystem", Integer.valueOf(reportSystem));
	//
	//		}
	//		//		hql += " and (reportReferenceId='' or reportReferenceId is null)";
	//		if (startingReportDate != null) {
	//			Calendar calendar = Calendar.getInstance();
	//			calendar.setTime(startingReportDate);
	//			int month = calendar.get(Calendar.MONTH) + 1;
	//			String dateCurr = String.valueOf(month);
	//			if (month < 10)
	//				dateCurr = String.valueOf("0" + month);
	//			hql += " and reportMonth >=:startDate";
	//			map.put("startDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
	//		}
	//
	//		if (endingReportDate != null) {
	//			Calendar calendar = Calendar.getInstance();
	//			calendar.setTime(endingReportDate);
	//			int month = calendar.get(Calendar.MONTH) + 1;
	//			String dateCurr = String.valueOf(month);
	//			if (month < 10)
	//				dateCurr = String.valueOf("0" + month);
	//			hql += " and reportMonth <=:endDate";
	//			map.put("endDate", String.valueOf(calendar.get(Calendar.YEAR)) + "/" + dateCurr);
	//
	//		}
	//		hql += " order by reportLastEditTime desc";
	//		Query query = getSession().createQuery(hql);
	//		if (map != null) {
	//			query.setProperties(map);
	//		}
	//		String sum = "";
	//		int fIndex = hql.indexOf("from");
	//		if (fIndex != -1) {
	//			sum = "select count(*) as count " + hql.substring(fIndex);
	//		}
	//		int oIndex = sum.indexOf("order by");
	//		if (oIndex != -1) {
	//			sum = sum.substring(0, oIndex);
	//		}
	//		Query querySum = getSession().createQuery(sum);
	//		if (map != null) {
	//			querySum.setProperties(map);
	//		}
	//		int count = ((Number) querySum.iterate().next()).intValue();
	//		if (page == null) {
	//			query.setFirstResult(0);
	//			query.setMaxResults(20);
	//		} else {
	//			query.setFirstResult(page.getFirstResult());
	//			query.setMaxResults(page.getPageSize());
	//
	//		}
	//		List<Report> findByHql = query.list();
	//
	//		if (page == null) {
	//			return new Result(1, 1, count, findByHql, new ArrayList<Event>());
	//		} else {
	//			return new Result(page.getPageNumber(), page.getPageSize(), count, findByHql, new ArrayList<Event>());
	//		}
	//	}

}
